package org.aileen.mybatis.mapper;

//通过MyBatis实现数据库的查询操作

import org.aileen.mybatis.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper//Mabitis提供的处理,与Spring无关,将对象交给Spring管理
public interface UserInfoMapper {


    @ResultMap("BaseMap")
    @Select("select * from userinfo")
    List<UserInfo> queryUserInfos();
//=====================================

    @ResultMap("BaseMap")
    //只有一个参数随便取名为eg:value也可以,此时也能将id传递给value
    @Select("select * from userinfo where id = ${value} ")
    UserInfo queryUserInfo(Integer id);
    //将id 赋值给 ? ,通过#{}实现id自增,就不需要用户手动写多个
    //通过 @Param("别名")可以给参数自定义一个名称

    @Select("select * from userinfo where username = #{username} ")
    UserInfo queryUserInfoByName(String username);

    //====================
    //含多个参数
    @Select("select * from userinfo where id = #{id} and username = #{name}")
    UserInfo queryUserInfoByIdAndName(Integer id, String name);

    //含多个参数,并使用@param()进行重命名
    @Select("select * from userinfo where id = #{param1} and username = #{param2}")
    UserInfo queryUserInfoByIdAndName2(@Param("id1") Integer id,@Param("name1") String name);

    //----------
    //增
    //以对象形式插入参数
    @Insert("insert into userinfo (username , `password` , age , gender , phone , delete_flag) " +//表名和MySQL匹配
            "values (#{username},#{password},#{age},#{gender},#{phone},#{deleteFlag})" )//参数名称应该和属性名称匹配
    //直接将?改成#{对象属性名称}即可获取到对象的值
    Integer insert1(UserInfo userInfo);

    //以对象形式(给对象取别名)插入参数
    //将id设置为自增->通过@Options()注解
    @Options(useGeneratedKeys = true , keyProperty = "id")
    @Insert("insert into userinfo (username , `password` , age , gender , phone , delete_flag) " +//表名和MySQL匹配
            "values (#{Aileen.username},#{Aileen.password},#{Aileen.age},#{Aileen.gender},#{Aileen.phone},#{Aileen.deleteFlag})" )//参数名称应该和属性名称匹配
    //直接将?改成#{对象属性名称}即可获取到对象的值
    Integer insert2 (@Param("Aileen") UserInfo userInfo);

    //----------
    //删
    //删除数据
    @Delete("delete from userinfo where id = #{id}")
    Integer deleteById(Integer id);

    //改
    //更新数据
    @Update("update userinfo set `password` = #{password} where id = #{id}")
    Integer update(UserInfo userInfo);

    //-------
    //查
    //查询
    /**
     * 方式1:取别名
     */
    @Select("select id , username, password, age, gender, phone, delete_flag as deleteFlag, create_time as createTime, update_time as updateTime" +
            " from userinfo")
    List<UserInfo> queryUserInfos2();

    /*
    方式2:注解
     */
    @Results(id = "BaseMap" , value = {
            @Result(column = "delete_flag" , property = "deleteFlag"),
            @Result(column = "create_time" , property = "createTime"),
            @Result(column = "update_time" , property = "updateTime"),
    })
    @Select("select * from userinfo")
    List<UserInfo> queryUserInfos3();


    @Select("select * from userinfo")
    List<UserInfo> queryUserInfos4();

    //演示SQL注入

    //使用#
    @Select("select * from userinfo where username = #{username} and password = #{password}")
    List <UserInfo> queryUserByNameAndPassword(String username , String password);

    //使用$
    @Select("select * from userinfo where username = '${username}' and password = '${password}'")
    List <UserInfo> queryUserByNameAndPassword2(String username , String password);

    //排序
    @Select("select * from userinfo order by id ${order} ")
    List<UserInfo> queryUserInfosOrderById(String order);

    //模糊查询
    @Select("select * from userinfo where username like '%${name}%'  ")
    List<UserInfo> queryUserInfosByLike1(String name);

    //但是考虑到SQL注入的安全性,模糊查询更推荐SQL中的关键字 CONCAT进行字符串拼接,从而避免SQL注入问题
    @Select("select * from userinfo where username like CONCAT(\"%\" , \"min\" , \"%\")")
    List<UserInfo> queryUserInfosByLike(String name);





}























